package net.huashitong.Indicators.dao;

import com.sirdc.modules.core.dao.GenericQuery;
import com.sirdc.modules.core.dao.StringDao;
import com.sirdc.modules.core.filter.BaseFilter;
import com.sirdc.modules.utils.ObjectUtils;
import com.sirdc.modules.utils.StringUtils;
import net.huashitong.Indicators.dataBean.IndexDataDTO;
import net.huashitong.Indicators.entity.IndexData;
import net.huashitong.until.Until;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public class IndexDataDao extends StringDao<IndexData> {


    /**
     * 获取数据库最新的日期
     *
     * @param codeType Y 月度，J季度，N 年度
     * @return IndexData
     */
    public String getNewCurrentDate(String codeType,String areaType) {
        GenericQuery query = createQuery("obj");
        query.append(" and obj.codeType=:codeType").setParam("codeType", codeType);
        query.append(" and obj.areaType=:areaType").setParam("areaType",areaType);
        query.setOrder("datetime", BaseFilter.DESC);
        IndexData indexData = query.firstResult();
        if (ObjectUtils.isBlank(indexData)) {
            return Until.getCurrentMonth();
        }
        return indexData.getDatetime();

    }

    public IndexData getIndexDataInfo(String codeID, String datetime, String areaCode, String codeType, String areaType) {
        GenericQuery query = createQuery("obj");
        query.append(" and obj.codeId=:CodeId").setParam("CodeId", codeID);
        query.append(" and obj.datetime=:datetime").setParam("datetime", datetime);
        query.append(" and obj.codeType=:codeType ").setParam("codeType", codeType);
        query.append(" and obj.areaCode=:areaCode").setParam("areaCode", areaCode);
        if (StringUtils.isNotBlank(areaType))
            query.append(" and obj.areaType=:areaType ").setParam("areaType", areaType);

        return query.singleResult();
    }

    public List<IndexData> getListIndexData(String codeID, String areaCode, String codeType, String datetime, String areaType) {
        GenericQuery query = createQuery("obj");
        query.append(" and obj.codeId=:CodeId").setParam("CodeId", codeID);
        query.append(" and obj.codeType=:codeType ").setParam("codeType", codeType);
        query.append(" and obj.areaCode=:areaCode").setParam("areaCode", areaCode);
        query.append(" and obj.areaType=:areaType ").setParam("areaType", areaType);
        if(ObjectUtils.isNotBlank(datetime)){
            query.append(" and obj.datetime like :datetime").setParam("datetime", datetime + "%");
        }
        query.setOrder("datetime", BaseFilter.ASC);
        return query.listResult();
    }

    /*
        获取数据
     */
    public List<IndexDataDTO> queryListData(String codeID, String datetime, String areaCode, String codeType, String areaType) {

        StringBuffer sql = new StringBuffer(" select a.code_id \"codeId\",b.code_name \"codeName\", a.cumulative_value \"cumulativeValue\" , ");
        sql.append("a.current_value \"currentValue\",a.cumulative_ratio \"cumulativeRatio\" , ");
        sql.append("a.area_code \"areaCode\",c.area_name \"areaName\", a.datetime \"datetime\" ,b.unit \"unit\" ,  b.explanation \"explanation\" ");
        sql.append(" ,a.area_type \"areaType\"  ");
        sql.append("from ac04 a ,ab01 b ,ba01 c ");
        sql.append("where a.code_id= b.code_id and c.area_code = a.area_code and b.code_type =a.code_type   and  a.code_type ='" + codeType + "'");
//        sql.append(" and a.area_type = c.area_type ");
        sql.append(" and b.code_type ='" + codeType + "'");
        sql.append(" and a.code_Id ='" + codeID + "'");
        if (StringUtils.isNotBlank(areaType)) {
            sql.append(" and a.area_type='" + areaType + "' ");
            sql.append(" and b.area_type='" + areaType + "' ");
            if ("D".equals(areaType)) {
                sql.append(" and c.parent_code ='GX35' ");
            }
        }
        if (StringUtils.isNotBlank(datetime)) {
            sql.append(" and a.datetime like'" + datetime + "%'");
        }
        if (StringUtils.isNotBlank(areaCode)) {
            sql.append(" and a.area_code='" + areaCode + "'");
        }
        if("GX350200".equals(areaCode)){
            sql.append(" and c.parent_code='GX35'");
        }
        sql.append(" order by a.datetime asc");
        SQLQuery query = getSession().createSQLQuery(sql.toString());
        query.setResultTransformer(Transformers.aliasToBean(IndexDataDTO.class));
        List<IndexDataDTO> list = (List<IndexDataDTO>) query.list();

        return list;
    }


    /**
     *    获取数据  增加了 中文名搜索
     *
     * @param codeID
     * @param datetime
     * @param areaCode
     * @param codeType
     * @param areaType
     * @param codeName
     * @return
     */

    public List<IndexDataDTO> queryListData(String codeID, String datetime, String areaCode, String codeType, String areaType,String codeName) {
        StringBuffer sql = new StringBuffer(" select a.code_id \"codeId\",b.code_name \"codeName\", a.cumulative_value \"cumulativeValue\" , ");
        sql.append("a.current_value \"currentValue\",a.cumulative_ratio \"cumulativeRatio\" , ");
        sql.append("a.area_code \"areaCode\",c.area_name \"areaName\", a.datetime \"datetime\" ,b.unit \"unit\" ,  b.explanation \"explanation\" ");
        sql.append(" ,a.area_type \"areaType\"  ");
        sql.append("from ac04 a ,ab01 b ,ba01 c ");
        sql.append("where a.code_id= b.code_id and c.area_code = a.area_code and b.code_type =a.code_type   and  a.code_type ='" + codeType + "'");
//        sql.append(" and a.area_type = c.area_type ");
        sql.append(" and b.code_type ='" + codeType + "'");
        if(StringUtils.isNotBlank(codeID))
            sql.append(" and a.code_Id ='" + codeID + "'");
        if (StringUtils.isNotBlank(areaCode)) {
            sql.append(" and a.area_code='" + areaCode + "'");
        }
        if (StringUtils.isNotBlank(areaType)) {
            sql.append(" and a.area_type='" + areaType + "' ");
            sql.append(" and b.area_type='" + areaType + "' ");
            if ("D".equals(areaType)) {
                sql.append(" and c.parent_code ='GX35' ");
            }
        }
        if (StringUtils.isNotBlank(datetime)) {
            sql.append(" and a.datetime like'" + datetime + "%'");
        }
        if("GX350200".equals(areaCode)){
            sql.append(" and c.parent_code='GX35'");
        }
        if (StringUtils.isNotBlank(codeName))
        {
            sql.append(" and b.code_name  ='"+codeName+"'" );
        }


        sql.append(" order by a.datetime asc");
        SQLQuery query = getSession().createSQLQuery(sql.toString());
        query.setResultTransformer(Transformers.aliasToBean(IndexDataDTO.class));
        List<IndexDataDTO> list = (List<IndexDataDTO>) query.list();

        return list;
    }

    /**
     * 删除数据 不带codeId
     *
     * @param yera
     * @param codeType
     * @param areaType
     */
    public void deleteIndexData(String yera, String codeType, String areaType) {
        StringBuffer sql = new StringBuffer();
        sql.append(" delete from IndexData obj ");
        sql.append(" where obj.datetime like:datetime");
        sql.append(" and obj.codeType=:codeType ");
        sql.append(" and obj.areaType=:areaType ");
        GenericQuery query = create(sql.toString());
        query.setParam("datetime", yera + "%");
        query.setParam("codeType", codeType);
        query.setParam("areaType", areaType);
        query.executeUpdate();
    }

    /**
     * 删除指标，带codeid
     * @param yera
     * @param codeType
     * @param areaType
     * @param codeId
     */
    public void deleteIndexData(String yera, String codeType, String areaType,String codeId) {
        StringBuffer sql = new StringBuffer();
        sql.append(" delete from IndexData obj ");
        sql.append(" where obj.datetime like:datetime");
        sql.append(" and obj.codeType=:codeType ");
        sql.append(" and obj.areaType=:areaType ");
        sql.append(" and obj.codeId=:codeId");
        GenericQuery query = create(sql.toString());
        query.setParam("datetime", yera + "%");
        query.setParam("codeType", codeType);
        query.setParam("areaType", areaType);
        query.setParam("codeId",codeId);
        query.executeUpdate();
    }

    /*
            删除重复数据
     */
    public void deleteRepeatData(String codeType, String areaType) {
        StringBuffer sql = new StringBuffer();
        sql.append(" delete from IndexData a ");
        sql.append(" where (a.codeId, a.datetime, a.codeType ,a.areaType ,a.areaCode ) in ");
        sql.append(" (select b.codeId, b.datetime, b.codeType,b.areaType,b.areaCode  ");
        sql.append(" from IndexData b ");
        sql.append(" where b.codeType =:codeType ");
        sql.append(" and b.areaType=:areaType ");
        sql.append(" group by b.codeId, b.datetime, b.codeType,b.areaType,b.areaCode   having count(1) > 1) ");
        sql.append(" and rowid not in (select min(rowid) ");
        sql.append(" from IndexData c ");
        sql.append(" where c.codeType =:codeType");
        sql.append(" and c.areaType=:areaType ");
        sql.append(" group by c.codeId, c.datetime, c.codeType,c.areaType,c.areaCode  ");
        sql.append(" having count(1) > 1) ");
        GenericQuery query = create(sql.toString());
        query.setParam("codeType", codeType);
        query.setParam("areaType", areaType);
        query.executeUpdate();
    }


    /**
     * 搜索数据
     *
     * @param codeID
     * @param datetime
     * @param areaCode
     * @param codeType
     * @return
     */
    public List<IndexDataDTO> getSearchListData(String codeID, String datetime, String areaCode, String codeType) {

        StringBuffer sql = new StringBuffer(" select a.code_id \"codeId\",b.code_name \"codeName\", a.cumulative_value \"cumulativeValue\" , ");
        sql.append("a.current_value \"currentValue\",a.cumulative_ratio \"cumulativeRatio\" , ");
        sql.append("a.area_code \"areaCode\",c.area_name \"areaName\", a.datetime \"datetime\" ,b.unit \"unit\" ,  b.explanation \"explanation\" ");
        sql.append(" ,a.area_type \"areaType\" ,a.code_type  \"codeType\" ");
        sql.append("from ac04 a ,ab01 b ,ba01 c ");
        sql.append("where a.code_id= b.code_id and c.area_code = a.area_code and b.code_type =a.code_type   and  a.code_type ='" + codeType + "'");
        sql.append(" and b.code_type ='" + codeType + "'");
        if (StringUtils.isNotBlank(codeID)) {
            sql.append(" and UPPER(b.code_name) like UPPER('%" + codeID + "%')");
        }

        if (StringUtils.isNotBlank(datetime)) {
            sql.append(" and UPPER(a.datetime) like UPPER('%" + datetime + "%')");

        }
        if (StringUtils.isNotBlank(areaCode)) {
            sql.append(" and UPPER(c.area_name) like UPPER('%" + areaCode + "%')");
        }
        SQLQuery query = getSession().createSQLQuery(sql.toString());
        query.setResultTransformer(Transformers.aliasToBean(IndexDataDTO.class));
        List<IndexDataDTO> list = (List<IndexDataDTO>) query.list();

        return list;
    }

    /**
     * 获取排名
     *
     * @param codeId
     * @param areacode
     * @param datetime
     * @param codeType
     * @param areaType
     * @return
     */
    public List<IndexDataDTO> getRank(String codeId, String areacode,
                                      String datetime, String codeType,
                                      String areaType, String rankunit,
                                      boolean pingtang) {

        /*if (StringUtils.isBlank(rankunit)) {
            rankunit = "cumulative_ratio";
        }*/
        String ptcode = "";
        if(pingtang){
            //福州包含平潭
            ptcode = "GX350126','GX350128";
        }else{
            ptcode = "GX350100";
        }

        StringBuffer sql = new StringBuffer();
        sql.append("select t.rank \"ranks\" ,t.code_id \"codeId\" ,t.area_code \"areaCode\" ,t.datetime \"datetime\"  ,t.cumulative_ratio  \"cumulativeRatio\",t.cumulative_value  \"cumulativeValue\" ");
        if (StringUtils.isBlank(rankunit)) {
            sql.append("from ( select rank() over(order by(cast(a.cumulative_ratio as numeric(10,1)) + 0) desc) rank, a.*  from ac04 a ");
        }else{
            rankunit = "cumulative_value";
            sql.append("from ( select rank() over(order by ( a." + rankunit + " +0) desc) rank, a.*  from ac04 a ");
        }

        sql.append("where code_id = '" + codeId + "' ");
        sql.append("and area_type = '" + areaType + "'");
        sql.append("and code_type = '" + codeType + "'   ");
        sql.append(" and  datetime ='" + datetime + "' and area_code not in ('" + ptcode + "')) t  ");
        if (StringUtils.isNotBlank(areacode))
            sql.append(" where t.area_code ='" + areacode + "' ");

        SQLQuery query = getSession().createSQLQuery(sql.toString());
        query.setResultTransformer(Transformers.aliasToBean(IndexDataDTO.class));
        List<IndexDataDTO> list = (List<IndexDataDTO>) query.list();

        return list;

    }


    public List<IndexDataDTO> getCitysValues(String codeId, String datetime, String codeType, String areaType) {
        StringBuffer sql = new StringBuffer();
        sql.append("  select dense_rank() over(order by t.cumulative_ratio desc ) \"ranks\",  t.area_code \"areaCode\", t.area_name \"areaName\",t.cumulative_ratio \"cumulativeRatio\" ,t.current_value \"currentValue\",t.cumulative_value \"cumulativeValue\",t.datetime \"datetime\" ,t.code_id \"codeId\",t.code_type \"codeType\" ,t.area_type \"areaType\", t2.code_name \"codeName\" ,t2.unit \"unit\"  from ( ");
        sql.append("  select b.area_code, b.area_name,a.cumulative_ratio,a.current_value,a.cumulative_value,a.datetime,a.code_id,a.code_type,a.area_type ");
        sql.append("  from ac04 a, ba01 b ");
        sql.append("  where 1=1 ");
        sql.append("  and  a.area_code(+) = b.area_code ");
        sql.append("  and b.area_type = '" + areaType + "'");
        sql.append(" and a.area_type(+) = '" + areaType + "'");
        sql.append("  and a.code_type(+)='" + codeType + "'");
        sql.append("  and a.code_id(+) = '" + codeId + "'");
        sql.append(" and a.datetime(+) = '" + datetime + "' ) t , ab01 t2 ");
        sql.append("  where t.code_id = t2.code_id (+) ");
        sql.append("  and t.area_type =t2.area_type (+) ");
        sql.append("  and t.code_type =t2.code_type(+) ");

        SQLQuery query = getSession().createSQLQuery(sql.toString());
        query.setResultTransformer(Transformers.aliasToBean(IndexDataDTO.class));
        List<IndexDataDTO> list = (List<IndexDataDTO>) query.list();
        return list;
    }

    public List<IndexDataDTO> getCitysValuesNoPt(String codeId, String datetime, String codeType, String areaType) {
        StringBuffer sql = new StringBuffer();
        sql.append("  select dense_rank() over(order by t.cumulative_ratio desc ) \"ranks\",  t.area_code \"areaCode\", t.area_name \"areaName\",t.cumulative_ratio \"cumulativeRatio\" ,t.current_value \"currentValue\",t.cumulative_value \"cumulativeValue\",t.datetime \"datetime\" ,t.code_id \"codeId\",t.code_type \"codeType\" ,t.area_type \"areaType\", t2.code_name \"codeName\" ,t2.unit \"unit\"  from ( ");
        sql.append("  select b.area_code, b.area_name,a.cumulative_ratio,a.current_value,a.cumulative_value,a.datetime,a.code_id,a.code_type,a.area_type ");
        sql.append("  from ac04 a, ba01 b ");
        sql.append("  where 1=1 ");
        sql.append("  and  a.area_code(+) = b.area_code ");
        sql.append("  and b.area_type = '" + areaType + "'");
        sql.append(" and a.area_type(+) = '" + areaType + "'");
        sql.append("  and a.code_type(+)='" + codeType + "'");
        sql.append("  and a.code_id(+) = '" + codeId + "'");
        sql.append(" and a.datetime(+) = '" + datetime + "' and b.area_code not in ('GX350126','GX350128')) t , ab01 t2 ");
        sql.append("  where t.code_id = t2.code_id (+) ");
        sql.append("  and t.area_type =t2.area_type (+) ");
        sql.append("  and t.code_type =t2.code_type(+) ");

        SQLQuery query = getSession().createSQLQuery(sql.toString());
        query.setResultTransformer(Transformers.aliasToBean(IndexDataDTO.class));
        List<IndexDataDTO> list = (List<IndexDataDTO>) query.list();
        return list;
    }





    public IndexData getRanks(String datetime, String codeType, String areaType, String codeId, String areaCode) {
        GenericQuery query = createQuery("obj");
        query.append(" and obj.datetime=:datetime").setParam("datetime", datetime);
        query.append(" and obj.codeId=:codeId").setParam("codeId", codeId);
        query.append(" and obj.codeType=:codeType ").setParam("codeType", codeType);
        query.append(" and obj.areaCode=:areaCode").setParam("areaCode", areaCode);
        query.append(" and obj.areaType=:areaType").setParam("areaType", areaType);
        return query.singleResult();
    }

    public List<IndexData> getNodataCode()
    {
        GenericQuery query  = createQuery("obj");
      //  query.append(" and obj.datetime ='2016' and obj.areaType ='D' and obj.codeType ='N' and lengthb(obj.codeId)>10  and currentValue is null ");
        query.append("   and datetime ='2016' and obj.areaType ='D' and obj.codeType ='N' and lengthb(obj.codeId)=9  and currentValue  is null ");
        return query.listResult();
    }
}
